package com.shengzhe.ostudent.utils.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.shengzhe.ostudent.common.exception.OsException;
import com.shengzhe.ostudent.utils.request.RequestLocalHolder;
import com.shengzhe.ostudent.utils.result.E;
import com.shengzhe.ostudent.utils.security.JwtUtils;

import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;


public class JDBCUtils {
    private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();

    private static final Properties sqlProperties;


    // 加载驱动和sql存放文件
    static {
        try {
            // 读取sql存放文件
            sqlProperties = new Properties();
            sqlProperties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("sql.properties"));
        } catch (IOException e) {
            throw new OsException(E.DATA_CODE_N54);
        }
    }

    public static String getSql(String key) {
        return sqlProperties.getProperty(key);
    }

    /**
     * 获取JDBC连接
     *
     * @return 连接对象
     */
    public static Connection getConnection() {
        try {

            Connection connection = RequestLocalHolder.getConnectionByThreadLocal();
            if (Objects.isNull(connection) || connection.isClosed()) {
                // 取出一条新连接
                connection = dataSource.getConnection();
                
                // 关闭自动提交
                 connection.setAutoCommit(false);
                RequestLocalHolder.setConnectionForThreadLocal(connection);
            }
            System.out.println(RequestLocalHolder.getConnectionByThreadLocal());
            return connection;
        } catch (SQLException e) {
            throw new OsException(E.DATA_CODE_N54);
        }
    }

    /**
     * 提交事务
     */
    public static void commit() {
        try {
            Connection connection = getConnection();
            if (!Objects.isNull(connection) && !connection.isClosed()) {
                connection.commit();
                releaseConnection();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 回滚事务
     */
    public static void rollback() {
        try {
            Connection connection = getConnection();
            if (!Objects.isNull(connection) && !connection.isClosed()) {
                connection.rollback();
                releaseConnection();

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 查询操作执行方法
     *
     * @param sql      查询语句
     * @param whereSql 查询条件语句
     * @return 结果集
     */
    public static ResultSet executeQuery(String sql, WhereSql whereSql, Object... params) {
        PreparedStatement preparedStatement;
        try {
            String selectSql = sql + whereSql.getStringWhereSql();
            System.out.println("@select >>> " + selectSql);
            preparedStatement = getConnection().prepareStatement(selectSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new OsException(E.DATA_CODE_N54);
        }
    }


    /**
     * 查询操作执行方法
     *
     * @param sql 查询语句
     * @return 结果集
     */
    public static ResultSet executeQuery(String sql, Object... params) {
        PreparedStatement preparedStatement;
        try {
            System.out.println("@select >>> " + sql);
            preparedStatement = getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            throw new OsException(E.DATA_CODE_N54);
        }
    }

    /**
     * 执行增删改操作
     *
     * @param sql    sql语句
     * @param params 参数
     * @return 影响行数
     */
    public static int executeUpdate(String sql, Object... params) {

        PreparedStatement preparedStatement;
        try {
            System.out.println("@execute >>> " + sql);
            preparedStatement = getConnection().prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }

            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new OsException(E.DATA_CODE_N54);
        }
    }

    /**
     * 执行增删改操作
     *
     * @param sql    sql语句
     * @param params 参数
     * @return 影响行数
     */
    public static int executeUpdate(String sql, WhereSql whereSql, Object... params) {
        PreparedStatement preparedStatement;
        try {
            String executeSql = sql + whereSql.getStringWhereSql();
            System.out.println("@execute >>> " + executeSql);
            preparedStatement = getConnection().prepareStatement(executeSql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }

            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new OsException(E.DATA_CODE_N54);
        }
    }


    /**
     * 释放资源
     *
     * @param resultSet 结果集对象
     */
    public static void releaseResultSet(ResultSet resultSet) {
        try {
            Statement statement = null;
            if (resultSet != null) {
                statement = resultSet.getStatement();
                resultSet.close(); // 关闭结果集对象
            }
            if (statement != null) {
                statement.close(); // 关闭Statement对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void releaseConnection() {
        Connection connection = getConnection();
        if (connection != null) {
            try {
                connection.close(); // 关闭数据库连接对象
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
